package com.huaxia.dao.realAct;

import com.huaxia.pojo.realAct.SaleDeptClassAct;

import org.apache.ibatis.annotations.Select;


import java.util.List;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/06/08 16:25
 */

public interface SaleDeptClassActMapper {

    @Select(" select a.saledeptsName,a.saledeptOldAct,b.saledeptOldNum,round(decode(b.saledeptOldNum,0,0,a.saledeptOldAct*100/b.saledeptOldNum),1)saledeptOldActYield,c.saledeptNewAct,d.saledeptNewNum,round(decode(d.saledeptNewNum,0,0,c.saledeptNewAct*100/d.saledeptNewNum),1)saledeptNewActYield  from\n" +
            "(select sb.saledeptsname,NVL(a.a,0)saledeptOldAct from\n" +
            "(select x.saledeptCODE,SUM(case when x.YCB>='10000' then 1 end)a FROM\n" +
            "( select a.saledeptCODE,NVL(a.cbm,0)-NVL(b.ytm,0) ycb from\n" +
            "(select t.saledeptCODE, t.AGENTCODE, SUM(WRITTENSTADPREM)cbm from FACT_YX_PREPREM_LIST_day t\n" +
            "where TO_CHAR(t.ISSUE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and TO_CHAR(t.EMPLOYDATE,'YYYY/MM')!=TO_CHAR(SYSDATE,'YYYY/MM') and t.RNFLAG='N' and t.AGENTSTATE='在职'\n" +
            "group by t.AGENTCODE,t.saledeptCODE)a left join\n" +
            "(select s.saledeptCODE, s.AGENTCODE, SUM(WRITTENSTADPREM)ytm from FACT_YX_PREPREM_LIST_day s\n" +
            "where TO_CHAR(s.YT_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and TO_CHAR(s.EMPLOYDATE,'YYYY/MM')!=TO_CHAR(SYSDATE,'YYYY/MM') and s.RNFLAG='N' and s.AGENTSTATE='在职'\n" +
            "group by s.AGENTCODE,s.saledeptCODE)b on a.AGENTCODE=b.AGENTCODE)x GROUP BY x.saledeptCODE)a right join SD_SALEDEPT sb on sb.saledeptCODE=a.saledeptCODE\n" +
            ")a,\n" +
            "(select sb.saledeptsname,NVL(a.saledeptOldNum,0)saledeptOldNum from\n" +
            "(select d.saledeptcode,count(d.saledeptcode)saledeptOldNum from D_AGENT_PROVINCE_YX2 d where AGENTSTATE='在职' and TO_CHAR(d.Employdate,'YYYY/MM')!=TO_CHAR(sysdate,'YYYY/MM') group by d.saledeptcode)a\n" +
            "right join SD_SALEDEPT sb on sb.saledeptcode=a.saledeptcode\n" +
            ")b,\n" +
            "(select sb.saledeptsname,NVL(a.a,0)saledeptNewAct from\n" +
            "(select x.saledeptCODE,SUM(case when x.YCB>='10000' then 1 end)a FROM\n" +
            "( select a.saledeptCODE,NVL(a.cbm,0)-NVL(b.ytm,0) ycb from\n" +
            "(select t.saledeptCODE, t.AGENTCODE, SUM(WRITTENSTADPREM)cbm from FACT_YX_PREPREM_LIST_day t\n" +
            "where TO_CHAR(t.ISSUE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and TO_CHAR(t.EMPLOYDATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and t.RNFLAG='N' and t.AGENTSTATE='在职'\n" +
            "group by t.AGENTCODE,t.saledeptCODE)a left join\n" +
            "(select s.saledeptCODE, s.AGENTCODE, SUM(WRITTENSTADPREM)ytm from FACT_YX_PREPREM_LIST_day s\n" +
            "where TO_CHAR(s.YT_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and TO_CHAR(s.EMPLOYDATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and s.RNFLAG='N' and s.AGENTSTATE='在职'\n" +
            "group by s.AGENTCODE,s.saledeptCODE)b on a.AGENTCODE=b.AGENTCODE)x GROUP BY x.saledeptCODE)a right join SD_SALEDEPT sb on sb.saledeptCODE=a.saledeptCODE\n" +
            ")c,\n" +
            "(select sb.saledeptsname,NVL(a.saledeptNewNum,0)saledeptNewNum from\n" +
            "(select d.saledeptcode,count(d.saledeptcode)saledeptNewNum from D_AGENT_PROVINCE_YX2 d where AGENTSTATE='在职' and TO_CHAR(d.Employdate,'YYYY/MM')=TO_CHAR(sysdate,'YYYY/MM') group by d.saledeptcode)a\n" +
            "right join SD_SALEDEPT sb on sb.saledeptcode=a.saledeptcode\n" +
            ")d where a.saledeptsname=b.saledeptsname and b.saledeptsname=c.saledeptsname and c.saledeptsname=d.saledeptsname order by c.saledeptNewAct DESC")
    List<SaleDeptClassAct> getSaleDeptClassAct();
}
